erDiagram
    %% ACCOUNTS MICROSERVICE
    CUSTOMERS ||--o{ ACCOUNTS : has
    ACCOUNTS ||--o{ TRANSACTIONS : contains
    ACCOUNTS }|--|| ACCOUNT_TYPES : is_of
    
   
    
    CUSTOMERS {
        int customer_id PK
        varchar keycloak_id
        varchar first_name
        varchar last_name
        date date_of_birth
        varchar address
        varchar city
        varchar state
        varchar zip_code
        varchar phone_number
        varchar email
        varchar password_hash
        varchar tax_id
        datetime created_at
        datetime updated_at
        tinyint is_active
    }
    
    ACCOUNTS {
        int account_id PK
        int customer_id FK
        int account_type_id FK
        varchar account_number
        decimal balance
        varchar currency
        date opening_date
        tinyint status
        datetime last_activity
        varchar notes
        datetime created_at
        datetime updated_at
    }
    
    ACCOUNT_TYPES {
        int account_type_id PK
        varchar name
        varchar description
        decimal interest_rate
        decimal minimum_balance
        decimal monthly_fee
        tinyint is_active
        datetime created_at
        datetime updated_at
    }
    
    TRANSACTIONS {
        int transaction_id PK
        int account_id FK
        decimal amount
        varchar transaction_type
        datetime transaction_date
        varchar description
        varchar reference_number
        int receiving_account_id FK
        varchar transaction_status
        datetime created_at
    }
    
    
    %% CARDS MICROSERVICE
    CARDS }|--|| ACCOUNT_REF : linked_to
    CARDS }|--|| CARD_TYPES : is_of
    CARDS ||--o{ CARD_TRANSACTIONS : has
    
    CUSTOMER_REF {
        int customer_id PK
        varchar first_name
        varchar last_name
        varchar email
        tinyint is_active
    }
    
    ACCOUNT_REF {
        int account_id PK
        varchar account_number
        int customer_id FK
    }
    
    CARDS {
        int card_id PK
        int account_id FK
        int card_type_id FK
        int card_status_id FK
        varchar card_number
        varchar cardholder_name
        date expiry_date
        varchar cvv
        decimal limit_amount
        varchar status_name
        varchar billing_address
        varchar billing_city
        varchar billing_state
        varchar billing_zip
        datetime issue_date
        tinyint is_contactless
        datetime created_at
        datetime updated_at
    }
    
    CARD_TYPES {
        int card_type_id PK
        varchar name
        varchar description
        decimal annual_fee
        decimal interest_rate
        int credit_score_requirement
        decimal default_limit
        tinyint is_active
        datetime created_at
        datetime updated_at
    }
    
    CARD_TRANSACTIONS {
        int transaction_id PK
        int card_id FK
        decimal amount
        varchar merchant_name
        varchar merchant_category
        varchar transaction_location
        datetime transaction_date
        varchar transaction_type
        varchar status
        datetime created_at
    }
    
    
    %% LOANS MICROSERVICE
    LOANS }|--|| CUSTOMER_REF : taken_by
    LOANS }|--|| LOAN_TYPES : is_of
    LOANS ||--o{ LOAN_PAYMENTS : has
    LOANS ||--o{ COLLATERAL : secured_by
    LOAN_APPLICATIONS }|--|| CUSTOMER_REF : submitted_by
    LOAN_APPLICATIONS }|--|| LOANS : results_in
    
    LOANS {
        int loan_id PK
        int customer_id FK
        int loan_type_id FK
        decimal principal_amount
        decimal outstanding_amount
        decimal interest_rate
        int term_months
        date start_date
        date end_date
        varchar payment_frequency
        decimal monthly_payment
        varchar status
        int loan_officer_id
        datetime created_at
        datetime updated_at
    }
    
    LOAN_TYPES {
        int loan_type_id PK
        varchar name
        varchar description
        decimal min_interest_rate
        decimal max_interest_rate
        int min_term
        int max_term
        decimal minimum_amount
        decimal maximum_amount
        tinyint requires_collateral
        decimal processing_fee_percent
        tinyint is_active
        datetime created_at
        datetime updated_at
    }
    
    LOAN_APPLICATIONS {
        int application_id PK
        int customer_id FK
        int loan_type_id FK
        decimal requested_amount
        int requested_term
        varchar purpose
        datetime application_date
        varchar status
        varchar rejection_reason
        int credit_score
        decimal monthly_income
        decimal monthly_expenses
        int loan_officer_id
        int loan_id FK
        datetime created_at
        datetime updated_at
    }
    
    LOAN_PAYMENTS {
        int payment_id PK
        int loan_id FK
        decimal amount
        decimal principal_component
        decimal interest_component
        decimal fees
        datetime payment_date
        datetime due_date
        varchar payment_method
        varchar status
        varchar reference_number
        datetime created_at
    }
